#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","oracle",{'RaiseError'=>1});

# ************************************* Création type_test ************************************* #
my $id_type_test;

#vérification que le type_test n'existe pas
$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM Type_test WHERE label LIKE '%Genotypage%'");


#s'il n'existe pas on le créé
if(not $id_type_test){
	$dbh->do("INSERT INTO Type_test(label) VALUES ('Genotypage')");
	$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM Type_test WHERE label LIKE '%Génotypage%'");
}

# ************************************* Parcours et récupération fichier excel ************************************* #

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;

foreach($workbook->worksheets()){
    $worksheet=$_;
}

my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min, $col_max) = $worksheet->col_range();

for my $row (1 .. $row_max){ #parcours lignes
	my $sexe;
	my $code;
	my $histo;
	my $mere;
	my @pere;
	my $date_n;
	my %test;
	
    for my $col ($col_min .. 8){ #parcours colonnes récup infos animal, portée, pères
		if($col eq 0){ #sexe
			my $cont=$worksheet->get_cell($row,$col);
			$sexe=$cont->value();
		}
		elsif($col eq 1){ #code_animal
			my $cont=$worksheet->get_cell($row,$col);
			my $temp=$cont->value();
			if($temp=~/^\d{1,4}$/){
				$code=$cont->value();
			}
			elsif($temp=~/(\d{1,4})-(\d{1,4})/){
				$code=$1;
				$histo=$2;
			}
		}	
		elsif($col eq 2){ #date_naissance
			my $cont=$worksheet->get_cell($row,$col);
			if($cont){
				$date_n=$cont->value();
			}
		}
		elsif($col eq 3){ #code_animal_mere
			my $cont=$worksheet->get_cell($row,$col);
			if($cont and $cont->value()=~/^F(\d\d\d)/){$mere=$1;}
			elsif($cont){$mere=$cont->value();}
		}
		elsif($col>=4 and $col<=7){ #code_animal_peres
			my $cont=$worksheet->get_cell($row,$col);
			if($cont and $cont->value()=~/^M(\d\d\d)/){
				push(@pere,$1);
			}
		}
	}
	for (my $col2=9; $col2<=$col_max; $col2+=4){
		my $cont=$worksheet->get_cell($row,$col2);
		my %marq;
		if($cont and $cont->value()=~/\d\d\d\d-\d\d-\d\d/){
			#date genotypage
			$marq{'date'}=$cont->value();
			#nom du marqueur
			my $cont2=$worksheet->get_cell(0,$col2);
			my $val=$cont2->value();
			my @tab=split('Génotypage ', $val);
			my $marqueur=$tab[1];
			#allele1
			my $cont3=$worksheet->get_cell($row,$col2+1);
			$marq{'allele1'}=$cont3->value();
			#allele2
			my $cont4=$worksheet->get_cell($row,$col2+2);
			$marq{'allele2'}=$cont4->value();
			#genotype
			my $cont5=$worksheet->get_cell($row,$col2+3);
			$marq{'genotype'}=$cont5->value();
			
			$test{$marqueur}={%marq};
		}
	}
	
	#~ foreach my $i (sort keys %test){
		#~ print "$i: ";
		#~ foreach my $k(sort keys %{$test{$i}}){
			#~ print "$k->$test{$i}{$k}, ";
		#~ }
		#~ print "\n";
	#~ }
	
	# ************************************* Création animal ************************************* #
    my $id_anim;
    
    #verification que l'animal n'existe pas
    my $prep_anim=$dbh->prepare("SELECT id_animal FROM Animal WHERE code_animal='$code'");
    $prep_anim->execute();
    while(my @ref=$prep_anim->fetchrow_array()){
		if($#ref>0){
			$id_anim=-1;
		}
		else{
			$id_anim=$ref[0];
		}
	}
	$prep_anim->finish();
    
    #si l'animal n'existe pas
    if(not $id_anim){
		if($histo and $date_n){ #si l'animal n'existe pas et qu'on a son code histo
			$dbh->do("INSERT INTO Animal(sexe, code_animal, code_histologique, date_naissance) VALUES('$sexe','$code',$histo,'$date_n')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' AND code_histologique=$histo");
		}
		elsif($date_n and not $histo){
			$dbh->do("INSERT INTO Animal(sexe,code_animal,date_naissance) VALUES('$sexe','$code','$date_n')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code'");
		}
		elsif(not $date_n and not $histo){
			$dbh->do("INSERT INTO Animal(sexe,code_animal) VALUES('$sexe','$code')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code'");
		}
		elsif($histo and not $date_n){
			$dbh->do("INSERT INTO Animal(sexe, code_animal, code_histologique) VALUES('$sexe','$code',$histo)");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' AND code_histologique=$histo");
		}
	}
	#s'il existe plusieurs animaux avec le même code_animal, on fait une nouvelle recherche avec le code_histo
	elsif($id_anim eq -1 and $histo){
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' AND code_histologique=$histo");
	}
	
	
	# ************************************* Création mere ************************************* #
	my $id_mere;
	
	#mère issu de l'animalerie
	if($mere and $mere=~/^\d\d\d$/){
		#vérification que la mère existe dans animal
		$id_mere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$mere'");
		#si elle n'existe pas on la créé
		if(not $id_mere){
			$dbh->do("INSERT INTO Animal(sexe,code_animal) VALUES('F','$mere')");
			$id_mere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$mere'");
		}
	}
	#mère d'une autre animalerie
	elsif($mere and $mere=~/^[A-Z]+$/){
		#vérification que la mère existe dans animal
		$id_mere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_autre_animalerie='$mere'");
		#si elle n'existe pas on la créé
		if(not $id_mere){ 
			$dbh->do("INSERT INTO Animal(sexe,code_autre_animalerie) VALUES('F','$mere')");
			$id_mere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_autre_animalerie='$mere'");
		}
	}
		
	# ************************************* Création pere ************************************* #
	if($#pere ne -1){
		foreach my $p(@pere){
			my $id_pere;
			my $exist;
			
			#verification de l'existance du pere dans animal
			$id_pere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$p'");
	
			#s'il n'existe pas on le créé
			if(not $id_pere){
				$dbh->do("INSERT INTO Animal(sexe,code_animal) VALUES('M','$p')");
				$id_pere=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$p'");
			}
			
			#~ #vérification que la portee avec ce pere n'existe pas
			#~ my $prep_pere=$dbh->prepare("SELECT id_animal FROM Est_le_pere WHERE id_animal=$id_pere and id_portee=$id_portee");
			#~ $prep_pere->execute();
			#~ while(my @ref=$prep_pere->fetchrow_array()){
				#~ if($#ref eq -1){$exist=0;}
				#~ else{$exist=1;}
			#~ }
			#~ $prep_anim->finish();
			#~ #si le pere n'est pas dans Est_le_pere on le créé
			#~ if(not $exist){
				#~ $dbh->do("INSERT INTO Est_le_pere(id_animal,id_portee) VALUES ($id_pere,$id_portee)");
			#~ }
		}
	}
	
	#ajout id_portee dans animal
	#~ $dbh->do("UPDATE Animal SET id_portee=$id_portee WHERE id_animal=$id_anim");
	
	if(%test){$dbh->do("UPDATE Animal SET test_paternite='true' WHERE id_animal=$id_anim");}
	else{$dbh->do("UPDATE Animal SET test_paternite='false' WHERE id_animal=$id_anim");}
	
	foreach my $i(sort keys %test){
		foreach my $k(sort keys %{$test{$i}}){
			# ************************************* Création test ************************************* #
			my $id_test;
			my $date=$test{$i}{'date'};
			$id_test=$dbh->selectrow_array("SELECT id_test FROM Test WHERE date_test=(E'$date') AND id_type_test=$id_type_test AND experimentateur='$exp'");
			
			if(not $id_test){
				$dbh->do("INSERT INTO Test(date_test,id_type_test,experimentateur) VALUES ('$date', $id_type_test,'$exp')");
				$id_test=$dbh->selectrow_array("SELECT id_test FROM Test WHERE date_test=(E'$date') AND id_type_test=$id_type_test AND experimentateur='$exp'");
			}
			
			
			# ************************************* Création resultat_test ************************************* #
			my $id_res;
			$id_res=$dbh->selectrow_array("SELECT id_res FROM resultat_test WHERE id_test=$id_test AND id_animal=$id_anim");
			
			if(not $id_res){
				$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test, $id_anim)");
				$id_res=$dbh->selectrow_array("SELECT id_res FROM resultat_test WHERE id_test=$id_test AND id_animal=$id_anim");
			}
			
			# ************************************* Création test_genotypage ************************************* #
			my $code_marq;
			$code_marq=$dbh->selectrow_array("SELECT code_marqueur FROM test_genotypage WHERE id_res=$id_res AND code_marqueur='$i'");
			
			if(not $code_marq){
				$dbh->do("INSERT INTO test_genotypage(id_res,code_marqueur, allele1,allele2,genotype) VALUES ($id_res,'$i', $test{$i}{'allele1'}, $test{$i}{'allele2'},'$test{$i}{'genotype'}')");
			}
		}
	}
}

$dbh->disconnect();
